--
-- PostgreSQL database dump
--

-- Dumped from database version 16.2
-- Dumped by pg_dump version 17.1

-- Started on 2025-05-18 01:19:21

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- TOC entry 236 (class 1255 OID 5910271)
-- Name: addcarsale(character varying, character varying, integer, integer, numeric); Type: FUNCTION; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE FUNCTION public.addcarsale(make_code character varying, model_code character varying, year integer, odo integer, price numeric) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO CarSales(MakeCode, ModelCode, BuiltYear, Odometer, Price, IsSold)
    VALUES (make_code, model_code, year, odo, price, FALSE);
END;
$$;


ALTER FUNCTION public.addcarsale(make_code character varying, model_code character varying, year integer, odo integer, price numeric) OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 235 (class 1255 OID 5910680)
-- Name: findcarsales(text); Type: FUNCTION; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE FUNCTION public.findcarsales(search text) RETURNS TABLE(carsale_id integer, make text, model text, builtyear integer, odometer integer, price numeric, issold boolean, sale_date text, buyer text, salesperson text)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    SELECT 
        cs.CarSaleID,
        mk.MakeName,
        md.ModelName,
        cs.BuiltYear,
        cs.Odometer,
        cs.Price,
        cs.IsSold,
        TO_CHAR(cs.SaleDate, 'DD-MM-YYYY'),
        CONCAT(c.FirstName, ' ', c.LastName),
        CONCAT(s.FirstName, ' ', s.LastName)
    FROM CarSales cs
    JOIN Make mk ON cs.MakeCode = mk.MakeCode
    JOIN Model md ON cs.ModelCode = md.ModelCode
    LEFT JOIN Customer c ON cs.BuyerID = c.CustomerID
    LEFT JOIN Salesperson s ON cs.SalespersonID = s.UserName
    WHERE mk.MakeName ILIKE '%' || search || '%'
       OR md.ModelName ILIKE '%' || search || '%'
       OR CONCAT(c.FirstName, ' ', c.LastName) ILIKE '%' || search || '%'
       OR CONCAT(s.FirstName, ' ', s.LastName) ILIKE '%' || search || '%'
    ORDER BY cs.IsSold, cs.SaleDate NULLS LAST;
END;
$$;


ALTER FUNCTION public.findcarsales(search text) OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 234 (class 1255 OID 5910679)
-- Name: getcarsalessummary(); Type: FUNCTION; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE FUNCTION public.getcarsalessummary() RETURNS TABLE(make text, model text, available_units integer, sold_units integer, total_sales numeric, last_purchased text)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    SELECT 
        mk.MakeName,
        md.ModelName,
        COUNT(*) FILTER (WHERE cs.IsSold = FALSE),
        COUNT(*) FILTER (WHERE cs.IsSold = TRUE),
        COALESCE(SUM(cs.Price) FILTER (WHERE cs.IsSold = TRUE), 0),
        TO_CHAR(MAX(cs.SaleDate) FILTER (WHERE cs.IsSold = TRUE), 'DD-MM-YYYY')
    FROM CarSales cs
    JOIN Make mk ON cs.MakeCode = mk.MakeCode
    JOIN Model md ON cs.ModelCode = md.ModelCode
    GROUP BY mk.MakeName, md.ModelName
    ORDER BY mk.MakeName, md.ModelName;
END;
$$;


ALTER FUNCTION public.getcarsalessummary() OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 221 (class 1255 OID 5910347)
-- Name: getcustomeridbyname(character varying); Type: FUNCTION; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE FUNCTION public.getcustomeridbyname(fullname character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
    cid VARCHAR;
BEGIN
    SELECT CustomerID INTO cid
    FROM Customer
    WHERE CONCAT(FirstName, ' ', LastName) = fullname;
    RETURN cid;
END;
$$;


ALTER FUNCTION public.getcustomeridbyname(fullname character varying) OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 222 (class 1255 OID 5910348)
-- Name: getsalespersonidbyname(character varying); Type: FUNCTION; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE FUNCTION public.getsalespersonidbyname(fullname character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
    uid VARCHAR;
BEGIN
    SELECT UserName INTO uid
    FROM Salesperson
    WHERE CONCAT(FirstName, ' ', LastName) = fullname;
    RETURN uid;
END;
$$;


ALTER FUNCTION public.getsalespersonidbyname(fullname character varying) OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 237 (class 1255 OID 5910272)
-- Name: updatecarsale(integer, character varying, character varying, date); Type: FUNCTION; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE FUNCTION public.updatecarsale(carsaleid integer, buyerid character varying, salespersonid character varying, saledate date) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    UPDATE CarSales
    SET BuyerID = buyerid,
        SalespersonID = salespersonid,
        SaleDate = saledate,
        IsSold = TRUE
    WHERE CarSaleID = carsaleid;
END;
$$;


ALTER FUNCTION public.updatecarsale(carsaleid integer, buyerid character varying, salespersonid character varying, saledate date) OWNER TO y25s1c9120_ruma0741;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 220 (class 1259 OID 5910651)
-- Name: carsales; Type: TABLE; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE TABLE public.carsales (
    carsaleid integer NOT NULL,
    makecode character varying(10) NOT NULL,
    modelcode character varying(10) NOT NULL,
    builtyear integer NOT NULL,
    odometer integer NOT NULL,
    price numeric(10,2) NOT NULL,
    issold boolean NOT NULL,
    buyerid character varying(10),
    salespersonid character varying(10),
    saledate date,
    CONSTRAINT carsales_builtyear_check CHECK (((builtyear >= 1950) AND ((builtyear)::numeric <= EXTRACT(year FROM CURRENT_DATE))))
);


ALTER TABLE public.carsales OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 219 (class 1259 OID 5910650)
-- Name: carsales_carsaleid_seq; Type: SEQUENCE; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE SEQUENCE public.carsales_carsaleid_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.carsales_carsaleid_seq OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 2647 (class 0 OID 0)
-- Dependencies: 219
-- Name: carsales_carsaleid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER SEQUENCE public.carsales_carsaleid_seq OWNED BY public.carsales.carsaleid;


--
-- TOC entry 216 (class 1259 OID 5910626)
-- Name: customer; Type: TABLE; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE TABLE public.customer (
    customerid character varying(10) NOT NULL,
    firstname character varying(50) NOT NULL,
    lastname character varying(50) NOT NULL,
    mobile character varying(20) NOT NULL
);


ALTER TABLE public.customer OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 217 (class 1259 OID 5910631)
-- Name: make; Type: TABLE; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE TABLE public.make (
    makecode character varying(5) NOT NULL,
    makename character varying(20) NOT NULL
);


ALTER TABLE public.make OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 218 (class 1259 OID 5910638)
-- Name: model; Type: TABLE; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE TABLE public.model (
    modelcode character varying(10) NOT NULL,
    modelname character varying(20) NOT NULL,
    makecode character varying(10) NOT NULL
);


ALTER TABLE public.model OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 215 (class 1259 OID 5910619)
-- Name: salesperson; Type: TABLE; Schema: public; Owner: y25s1c9120_ruma0741
--

CREATE TABLE public.salesperson (
    username character varying(10) NOT NULL,
    password character varying(20) NOT NULL,
    firstname character varying(50) NOT NULL,
    lastname character varying(50) NOT NULL
);


ALTER TABLE public.salesperson OWNER TO y25s1c9120_ruma0741;

--
-- TOC entry 2470 (class 2604 OID 5910654)
-- Name: carsales carsaleid; Type: DEFAULT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.carsales ALTER COLUMN carsaleid SET DEFAULT nextval('public.carsales_carsaleid_seq'::regclass);


--
-- TOC entry 2641 (class 0 OID 5910651)
-- Dependencies: 220
-- Data for Name: carsales; Type: TABLE DATA; Schema: public; Owner: y25s1c9120_ruma0741
--

COPY public.carsales (carsaleid, makecode, modelcode, builtyear, odometer, price, issold, buyerid, salespersonid, saledate) FROM stdin;
1	MB	cclass	2020	64210	72000.00	t	c001	jdoe	2024-03-01
2	MB	eclass	2019	31210	89000.00	f	\N	\N	\N
3	TOY	camry	2021	98200	37200.00	t	c123	brown	2023-12-07
4	TOY	corolla	2022	65000	35000.00	t	c910	jdoe	2024-09-21
5	LR	defender	2018	115000	97000.00	f	\N	\N	\N
6	VW	golf	2023	22000	33000.00	t	c233	jdoe	2023-11-06
7	LEX	nx	2020	67000	79000.00	t	c321	brown	2025-01-01
8	LR	discosport	2021	43080	85000.00	t	c211	ksmith1	2021-01-27
9	TOY	rav4	2019	92900	48000.00	f	\N	\N	\N
10	MB	aclass	2022	47000	57000.00	t	c199	jdoe	2025-03-01
11	LEX	ux	2023	23000	70000.00	t	c899	brown	2023-01-01
12	VW	passat	2020	63720	42000.00	f	\N	\N	\N
13	MB	eclass	2021	12000	155000.00	t	c002	ksmith1	2024-10-01
14	LR	rangerover	2017	60000	128000.00	f	\N	\N	\N
15	TOY	camry	2025	10	49995.00	f	\N	\N	\N
16	LR	discosport	2022	53000	89900.00	f	\N	\N	\N
17	MB	cclass	2023	55000	82100.00	f	\N	\N	\N
18	MB	aclass	2025	5	78000.00	f	\N	\N	\N
19	MB	aclass	2015	8912	12000.00	t	c199	jdoe	2020-03-11
20	TOY	camry	2024	21000	42000.00	f	\N	\N	\N
21	LEX	gx	2025	6	128085.00	f	\N	\N	\N
22	MB	eclass	2019	99220	105000.00	f	\N	\N	\N
23	VW	golf	2023	53849	43000.00	f	\N	\N	\N
24	MB	cclass	2022	89200	62000.00	f	\N	\N	\N
25	MB	aclass	2009	97500	13500.00	f	\N	\N	\N
\.


--
-- TOC entry 2637 (class 0 OID 5910626)
-- Dependencies: 216
-- Data for Name: customer; Type: TABLE DATA; Schema: public; Owner: y25s1c9120_ruma0741
--

COPY public.customer (customerid, firstname, lastname, mobile) FROM stdin;
c001	David	Wilson	4455667788
c899	Eva	Taylor	5566778899
c199	Frank	Anderson	6677889900
c910	Grace	Thomas	7788990011
c002	Stan	Martinez	8899001122
c233	Laura	Roberts	9900112233
c123	Charlie	Davis	7712340011
c321	Jane	Smith	9988990011
c211	Alice	Johnson	7712222221
\.


--
-- TOC entry 2638 (class 0 OID 5910631)
-- Dependencies: 217
-- Data for Name: make; Type: TABLE DATA; Schema: public; Owner: y25s1c9120_ruma0741
--

COPY public.make (makecode, makename) FROM stdin;
MB	Mercedes Benz
TOY	Toyota
VW	Volkswagen
LEX	Lexus
LR	Land Rover
\.


--
-- TOC entry 2639 (class 0 OID 5910638)
-- Dependencies: 218
-- Data for Name: model; Type: TABLE DATA; Schema: public; Owner: y25s1c9120_ruma0741
--

COPY public.model (modelcode, modelname, makecode) FROM stdin;
aclass	A Class	MB
cclass	C Class	MB
eclass	E Class	MB
camry	Camry	TOY
corolla	Corolla	TOY
rav4	RAV4	TOY
defender	Defender	LR
rangerover	Range Rover	LR
discosport	Discovery Sport	LR
golf	Golf	VW
passat	Passat	VW
troc	T Roc	VW
ux	UX	LEX
gx	GX	LEX
nx	NX	LEX
\.


--
-- TOC entry 2636 (class 0 OID 5910619)
-- Dependencies: 215
-- Data for Name: salesperson; Type: TABLE DATA; Schema: public; Owner: y25s1c9120_ruma0741
--

COPY public.salesperson (username, password, firstname, lastname) FROM stdin;
jdoe	Pass1234	John	Doe
brown	Passwxyz	Bob	Brown
ksmith1	Pass5566	Karen	Smith
\.


--
-- TOC entry 2648 (class 0 OID 0)
-- Dependencies: 219
-- Name: carsales_carsaleid_seq; Type: SEQUENCE SET; Schema: public; Owner: y25s1c9120_ruma0741
--

SELECT pg_catalog.setval('public.carsales_carsaleid_seq', 25, true);


--
-- TOC entry 2487 (class 2606 OID 5910657)
-- Name: carsales carsales_pkey; Type: CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.carsales
    ADD CONSTRAINT carsales_pkey PRIMARY KEY (carsaleid);


--
-- TOC entry 2477 (class 2606 OID 5910630)
-- Name: customer customer_pkey; Type: CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.customer
    ADD CONSTRAINT customer_pkey PRIMARY KEY (customerid);


--
-- TOC entry 2479 (class 2606 OID 5910637)
-- Name: make make_makename_key; Type: CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.make
    ADD CONSTRAINT make_makename_key UNIQUE (makename);


--
-- TOC entry 2481 (class 2606 OID 5910635)
-- Name: make make_pkey; Type: CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.make
    ADD CONSTRAINT make_pkey PRIMARY KEY (makecode);


--
-- TOC entry 2483 (class 2606 OID 5910644)
-- Name: model model_modelname_key; Type: CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.model
    ADD CONSTRAINT model_modelname_key UNIQUE (modelname);


--
-- TOC entry 2485 (class 2606 OID 5910642)
-- Name: model model_pkey; Type: CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.model
    ADD CONSTRAINT model_pkey PRIMARY KEY (modelcode);


--
-- TOC entry 2473 (class 2606 OID 5910625)
-- Name: salesperson salesperson_firstname_lastname_key; Type: CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.salesperson
    ADD CONSTRAINT salesperson_firstname_lastname_key UNIQUE (firstname, lastname);


--
-- TOC entry 2475 (class 2606 OID 5910623)
-- Name: salesperson salesperson_pkey; Type: CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.salesperson
    ADD CONSTRAINT salesperson_pkey PRIMARY KEY (username);


--
-- TOC entry 2489 (class 2606 OID 5910668)
-- Name: carsales carsales_buyerid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.carsales
    ADD CONSTRAINT carsales_buyerid_fkey FOREIGN KEY (buyerid) REFERENCES public.customer(customerid);


--
-- TOC entry 2490 (class 2606 OID 5910658)
-- Name: carsales carsales_makecode_fkey; Type: FK CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.carsales
    ADD CONSTRAINT carsales_makecode_fkey FOREIGN KEY (makecode) REFERENCES public.make(makecode);


--
-- TOC entry 2491 (class 2606 OID 5910663)
-- Name: carsales carsales_modelcode_fkey; Type: FK CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.carsales
    ADD CONSTRAINT carsales_modelcode_fkey FOREIGN KEY (modelcode) REFERENCES public.model(modelcode);


--
-- TOC entry 2492 (class 2606 OID 5910673)
-- Name: carsales carsales_salespersonid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.carsales
    ADD CONSTRAINT carsales_salespersonid_fkey FOREIGN KEY (salespersonid) REFERENCES public.salesperson(username);


--
-- TOC entry 2488 (class 2606 OID 5910645)
-- Name: model model_makecode_fkey; Type: FK CONSTRAINT; Schema: public; Owner: y25s1c9120_ruma0741
--

ALTER TABLE ONLY public.model
    ADD CONSTRAINT model_makecode_fkey FOREIGN KEY (makecode) REFERENCES public.make(makecode);


-- Completed on 2025-05-18 01:19:22

--
-- PostgreSQL database dump complete
--

